--查询脚本
select * from [dbo].[Applications]
select * from [dbo].[Companies]
select * from [dbo].[JobPostings]
select * from [dbo].[JobPostingsAudit]
select * from [dbo].[Resumes]
select * from [dbo].[Users]


--添加脚本
INSERT into [dbo].[Applications] VALUES (@ApplicantID, @JobPostingID, @ResumeID, @ApplicationStatus)  --Pending  ||  Accepted   ||  Rejected
INSERT into [dbo].[Companies] VALUES (@UserID, @CompanyName, @ContactPerson, @ContactPhone, @CompanyDescription, @CompanyAddress, @Industry)
INSERT into [dbo].[JobPostings] VALUES (@CompanyID, @JobTitle, @JobType, @Locations, @Salary, @JobDescription)
INSERT into [dbo].[JobPostingsAudit] VALUES (@JobPostingID, @AdminID)
INSERT into [dbo].[Resumes] VALUES (@ApplicantID, @FullName, @Email, @Phone, @YearsOfExperience, @Introduction, @ResumeFile)
INSERT into [dbo].[Users] VALUES (@Username, @Password, @Role, @Email)



--更新脚本
update [dbo].[Applications] set ApplicantID=@ApplicantID, JobPostingID=@JobPostingID, ResumeID=@ResumeID, ApplicationStatus=@ApplicationStatus where [ApplicationID] = @ApplicationID
update [dbo].[Companies] set UserID=@UserID, CompanyName=@CompanyName, ContactPerson=@ContactPerson, ContactPhone=@ContactPhone, CompanyDescription=@CompanyDescription, CompanyAddress=@CompanyAddress, Industry=@Industry where [CompanyID] = @CompanyID
update [dbo].[JobPostings] set CompanyID=@CompanyID, JobTitle=@JobTitle, JobType=@JobType, Locations=@Locations, Salary=@Salary, JobDescription=@JobDescription where [JobPostingID] = @JobPostingID
update [dbo].[JobPostingsAudit] set JobPostingID=@JobPostingID, AdminID=@AdminID where [AuditID] = @AuditID
update [dbo].[Resumes] set ApplicantID=@ApplicantID, FullName=@FullName, Email=@Email, Phone=@Phone, YearsOfExperience=@YearsOfExperience, Introduction=@Introduction, ResumeFile=@ResumeFile where [ResumeID] = @ResumeID
update [dbo].[Users] set Username=@Username, Password=@Password, Role=@Role, Email=@Email where [UserID] = @UserID



--删除脚本
delete from [dbo].[Applications] where [ApplicationID] = @ApplicationID
delete from [dbo].[Companies] where [CompanyID] = @CompanyID
delete from [dbo].[JobPostings] where [JobPostingID] = @JobPostingID
delete from [dbo].[JobPostingsAudit] where [AuditID] = @AuditID
delete from [dbo].[Resumes] where [ResumeID] = @ResumeID
delete from [dbo].[Users] where [UserID] = @UserID



--多表联合查询
--1. 查询每个公司的职位列表
SELECT c.CompanyName, jp.JobTitle, jp.JobType, jp.Locations, jp.Salary
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID;

--2. 查询每个职位的申请人数
SELECT c.CompanyName, jp.JobTitle, jp.JobType, jp.Locations, jp.Salary
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID;

--3. 查询每个应聘者的申请状态
SELECT u.Username AS Applicant, r.FullName, a.ApplicationStatus, jp.JobTitle
FROM [dbo].[Users] u
JOIN [dbo].[Resumes] r ON u.UserID = r.ApplicantID
JOIN [dbo].[Applications] a ON r.ResumeID = a.ResumeID
JOIN [dbo].[JobPostings] jp ON a.JobPostingID = jp.JobPostingID;

--4. 查询每个职位的审核状态
SELECT jp.JobTitle, u.Username AS Admin, jpa.AuditID
FROM [dbo].[JobPostings] jp
JOIN [dbo].[JobPostingsAudit] jpa ON jp.JobPostingID = jpa.JobPostingID
JOIN [dbo].[Users] u ON jpa.AdminID = u.UserID;

--5. 查询特定公司的所有职位
SELECT c.CompanyName, jp.JobTitle, jp.JobType, jp.Salary
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID
WHERE c.CompanyName = N'腾飞科技';

--6. 查询每个职位的应聘者信息
SELECT jp.JobTitle, u.Username AS Applicant, r.FullName, r.YearsOfExperience
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--7. 查询每个公司的招聘者信息
SELECT c.CompanyName, u.Username AS Recruiter, u.Email
FROM [dbo].[Companies] c
JOIN [dbo].[Users] u ON c.UserID = u.UserID;

--8. 查询每个职位的申请状态统计
SELECT jp.JobTitle, COUNT(CASE WHEN a.ApplicationStatus = 'Pending' THEN 1 END) AS Pending,
       COUNT(CASE WHEN a.ApplicationStatus = 'Accepted' THEN 1 END) AS Accepted,
       COUNT(CASE WHEN a.ApplicationStatus = 'Rejected' THEN 1 END) AS Rejected
FROM [dbo].[JobPostings] jp
LEFT JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
GROUP BY jp.JobTitle;

--9. 查询每个应聘者的简历信息
SELECT u.Username AS Applicant, r.FullName, r.Email, r.Phone, r.YearsOfExperience, r.Introduction
FROM [dbo].[Users] u
JOIN [dbo].[Resumes] r ON u.UserID = r.ApplicantID;

--10. 查询特定职位的所有申请
SELECT jp.JobTitle, u.Username AS Applicant, r.FullName, a.ApplicationStatus
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID
WHERE jp.JobTitle = N'软件工程师';

--11. 查询每个职位的平均工作经验要求
SELECT jp.JobTitle, AVG(r.YearsOfExperience) AS AvgExperience
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
GROUP BY jp.JobTitle;

--12. 查询特定公司的所有申请
SELECT c.CompanyName, jp.JobTitle, u.Username AS Applicant, a.ApplicationStatus
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Users] u ON a.ApplicantID = u.UserID
WHERE c.CompanyName = N'阿里巴巴集团';

--13. 查询每个职位的最高薪资
SELECT jp.JobTitle, MAX(jp.Salary) AS MaxSalary
FROM [dbo].[JobPostings] jp
GROUP BY jp.JobTitle;

--14. 查询每个公司的招聘者和职位数量
SELECT c.CompanyName, u.Username AS Recruiter, COUNT(jp.JobPostingID) AS JobCount
FROM [dbo].[Companies] c
JOIN [dbo].[Users] u ON c.UserID = u.UserID
LEFT JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID
GROUP BY c.CompanyName, u.Username;

--15. 查询每个应聘者的申请职位数量
SELECT u.Username AS Applicant, COUNT(a.ApplicationID) AS JobApplicationCount
FROM [dbo].[Users] u
JOIN [dbo].[Applications] a ON u.UserID = a.ApplicantID
GROUP BY u.Username;

--16. 查询每个职位的申请者数量（按公司分组）
SELECT c.CompanyName, jp.JobTitle, COUNT(a.ApplicationID) AS ApplicantCount
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID
LEFT JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
GROUP BY c.CompanyName, jp.JobTitle;

--17. 查询每个职位的申请者工作经验分布
SELECT jp.JobTitle, 
       COUNT(CASE WHEN r.YearsOfExperience < 3 THEN 1 END) AS Junior,
       COUNT(CASE WHEN r.YearsOfExperience BETWEEN 3 AND 5 THEN 1 END) AS MidLevel,
       COUNT(CASE WHEN r.YearsOfExperience > 5 THEN 1 END) AS Senior
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
GROUP BY jp.JobTitle;

--18. 查询每个公司的职位审核状态
SELECT c.CompanyName, jp.JobTitle, u.Username AS Admin, jpa.AuditID
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID
JOIN [dbo].[JobPostingsAudit] jpa ON jp.JobPostingID = jpa.JobPostingID
JOIN [dbo].[Users] u ON jpa.AdminID = u.UserID;

--19. 查询每个职位的申请者联系方式
SELECT jp.JobTitle, u.Username AS Applicant, u.Email, u.Role
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Users] u ON a.ApplicantID = u.UserID;

--20. 查询每个职位的申请者简历文件路径
SELECT jp.JobTitle, u.Username AS Applicant, r.ResumeFile
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--21. 查询每个职位的申请者工作经验和申请状态
SELECT jp.JobTitle, u.Username AS Applicant, r.YearsOfExperience, a.ApplicationStatus
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--22. 查询每个职位的申请者自我介绍
SELECT jp.JobTitle, u.Username AS Applicant, r.Introduction
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--23. 查询每个职位的申请者联系方式和申请状态
SELECT jp.JobTitle, u.Username AS Applicant, u.Email, a.ApplicationStatus
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Users] u ON a.ApplicantID = u.UserID;

--24. 查询每个职位的申请者工作经验和联系方式
SELECT jp.JobTitle, u.Username AS Applicant, u.Email, r.YearsOfExperience
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--25. 查询每个职位的申请者简历文件路径和申请状态
SELECT jp.JobTitle, u.Username AS Applicant, r.ResumeFile, a.ApplicationStatus
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--26. 查询每个职位的申请者工作经验和自我介绍
SELECT jp.JobTitle, u.Username AS Applicant, r.YearsOfExperience, r.Introduction
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--27. 查询每个职位的申请者联系方式和自我介绍
SELECT jp.JobTitle, u.Username AS Applicant, u.Email, r.Introduction
FROM [dbo].[JobPostings] jp
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--28. 查询每个职位的申请者工作经验和申请状态（按公司分组）
SELECT c.CompanyName, jp.JobTitle, u.Username AS Applicant, r.YearsOfExperience, a.ApplicationStatus
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;

--29. 查询每个职位的申请者联系方式和申请状态（按公司分组）
SELECT c.CompanyName, jp.JobTitle, u.Username AS Applicant, u.Email, a.ApplicationStatus
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Users] u ON a.ApplicantID = u.UserID;

--30. 查询每个职位的申请者简历文件路径和申请状态（按公司分组）
SELECT c.CompanyName, jp.JobTitle, u.Username AS Applicant, r.ResumeFile, a.ApplicationStatus
FROM [dbo].[Companies] c
JOIN [dbo].[JobPostings] jp ON c.CompanyID = jp.CompanyID
JOIN [dbo].[Applications] a ON jp.JobPostingID = a.JobPostingID
JOIN [dbo].[Resumes] r ON a.ResumeID = r.ResumeID
JOIN [dbo].[Users] u ON r.ApplicantID = u.UserID;